package com.ruoyi.web.controller.client;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.extra.mail.MailAccount;
import cn.hutool.extra.mail.MailUtil;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.framework.timetaskuntil.ApplicationContextUtil;
import com.ruoyi.framework.web.domain.server.Sys;
import com.ruoyi.system.domain.SysDept;
import com.ruoyi.yf.domain.Report;
import com.ruoyi.yf.domain.YfDept;
import com.ruoyi.yf.domain.YfDistrictStatistical;
import com.ruoyi.yf.domain.mobile.ResponseData;
import com.ruoyi.yf.service.IReportservice;
import com.ruoyi.yf.service.IYfDistrictStatisticalService;
import org.apache.http.Consts;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ByteArrayResource;
import org.springframework.core.io.InputStreamSource;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.stereotype.Component;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

import javax.mail.internet.MimeUtility;
import java.awt.*;
import java.io.*;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;


/**
 *
 * 接口 - 定时任务controller -- 定时生成两个统计excel发至邮箱、定时统计石家庄各区县每天产量、转运量、截止到当天的暂存量
 * a0e33a44-e610-4a1d-ab0e-5387710e2ba2
 */
@RestController
@RequestMapping("/client/scheduler")
@Component
public class SchedulerTaskController {


    @Autowired
    private IReportservice yfReportsService;

    @Autowired
    private IYfDistrictStatisticalService yfDistrictStatisticalService;




    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    SimpleDateFormat sdfday = new SimpleDateFormat("yyyy-MM-dd");
    SimpleDateFormat sdfzday = new SimpleDateFormat("yyyy年M月d日");
    SimpleDateFormat sdfztime = new SimpleDateFormat("yyyy年M月d日 HH:mm:ss");



    // 定时生成两个统计excel发至邮箱,三个参数：地区id、开始时间、结束时间
    //1f4ab865-ce66-490f-8bf4-69f458303745
    @RequestMapping("/createYFStatisticalExcelSendEmail")
    @ResponseBody
    public ResponseData createYFStatisticalExcelSendEmail(Long districtId,String startTime, String endTime) {

        System.out.println( "  地区id：" + districtId +  "  开始时间：" + startTime + "  结束时间：" + endTime   );


        yfReportsService = (IReportservice) ApplicationContextUtil.getBean("yfReportsService");

        // 开始、结束时间
        Date date = new Date();
        String nowtime = sdf.format(date);
        if(StringUtils.isEmpty(startTime)){
            startTime = "2020-08-05";
        }else{
            try{
                sdfday.parse(startTime);
            }catch (Exception e){
                startTime = "2020-08-05";
            }
        }
        if(StringUtils.isEmpty(endTime)){
            endTime = nowtime.substring(0,10);    // 取到天
        }else{
            try{
                sdf.parse(endTime);
            }catch (Exception e){
                endTime = nowtime.substring(0,10);    // 取到天
            }
        }

        // 格式化时间字符串 -- excel中使用
        String excelStartTime;
        String excelEndTime = sdfztime.format( date );
        try{
            excelStartTime = sdfzday.format( sdfday.parse(startTime) );
        }catch (Exception e){
            excelStartTime = "2020年8月5日";
        }


        // 地区id
        if(districtId == null){
            districtId = 1301L;    // 石家庄市的地区id
        }

        // 下辖机构状况
        List<Report> staticlist = yfReportsService.cityStatistical(districtId, startTime, endTime);
        // System.out.println("  下辖机构状况: " + staticlist.size() );


        // 医废种类分析
        List<Report> yftypestaticlist = yfReportsService.cityTrashTypeAll(districtId, startTime, endTime);
        // System.out.println("  医废种类分析: " + yftypestaticlist.size() );


        // 生成excel -- 下辖机构状况  27行  6列
        SXSSFWorkbook statisticalBook = createCityStatisticalExcel(staticlist, excelStartTime, excelEndTime);

        // 生成excel -- 医废种类分析  27行  9列
        SXSSFWorkbook yftypestaticBook = createCityYFTypeStaticExcel(yftypestaticlist, excelStartTime, excelEndTime);

        try {

//            String fileName = "石家庄各区县医废产量、转运量、暂存量统计"
//                    + nowtime.substring(0,16).replace(":","点") +"分.xlsx";
            String fileName = "石家庄各区县医废产量转运量及暂存量统计" + nowtime.substring(0,10) + ".xlsx";
            System.out.println("   ----   " + fileName );
            // 一、写文件到本地
            // String filePath = "C:\\Users\\Administrator\\Desktop\\";
            // FileOutputStream outFile = new FileOutputStream( filePath + fileName );     // 文件名不能出现：冒号等特殊字符
            // 二、输出到file发到邮箱
            File file = new File( new String(fileName.getBytes("utf-8"),System.getProperty("file.encoding")) );
            FileOutputStream outFile = new FileOutputStream(file);     // 文件名不能出现：冒号等特殊字符
            // 共有
            statisticalBook.write(outFile);
            statisticalBook.dispose();
            outFile.close();


//            String fileName2 = "石家庄各区县各医废类型统计"
//                    + nowtime.substring(0,16).replace(":","点") +"分.xlsx";
            String fileName2 = "石家庄各区县各类型医废统计" + nowtime.substring(0,10) + ".xlsx";
            System.out.println("   ----   " + fileName2 );
            // 一、写文件到本地
            // String filePath2 = "C:\\Users\\Administrator\\Desktop\\";
            // FileOutputStream outFile2 = new FileOutputStream( filePath2 + fileName2 );     // 文件名不能出现：冒号等特殊字符
            // 二、输出到file发到邮箱
            File file2 = new File( new String(fileName2.getBytes("utf-8"),System.getProperty("file.encoding")) );
            FileOutputStream outFile2 = new FileOutputStream(file2);     // 文件名不能出现：冒号等特殊字符
            // 共有
            yftypestaticBook.write(outFile2);
            yftypestaticBook.dispose();
            outFile2.close();


            // HuTool 发邮件
            MailAccount account = new MailAccount();
            account.setHost("smtp.163.com");
            account.setPort(25);
            account.setAuth(true);
            account.setCharset(Consts.UTF_8);
            account.setFrom("liasdasdasdasdasd@163.com");
            account.setUser("liasdasdasdasdasd@163.com");
            account.setPass("VXYLHJNAKXMKRVDV"); // 密码
            MailUtil.send(account, CollUtil.newArrayList("liasdasdasdasdasd@163.com","751116120@qq.com" ),
                    "医废统计", "医废统计 "+ excelStartTime +" 至 " + excelEndTime ,
                    false, file , file2 );




        } catch (Exception e) {
            System.out.println("-----导出excel异常了！-------");
            e.printStackTrace();
        }


        return ResponseData.successY("提交成功");
    }


    /**
     *  生成excel -- 下辖机构状况  27行  5列
     *  65ceceb1-5c1f-49c0-ad02-3a4fe8e8d778
     */
    private SXSSFWorkbook createCityStatisticalExcel(List<Report> staticlist, String excelStartTime, String excelEndTime){
        SXSSFWorkbook book = null;
        try {
            // 输出流
            // os = new FileOutputStream(excelPath);
            // 创建工作区(2007)       创建工作区(97-2003)    HSSFWorkbook
            book = new SXSSFWorkbook(800);

            // 字体样式1 -- 加粗标题字体
            Font font1 = book.createFont();
            font1.setBold(true);    // 加粗
            font1.setFontHeightInPoints((short)13);
            // 字体样式2 -- 普通加粗字体
            Font font2 = book.createFont();
            font2.setBold(true);    // 加粗
            font2.setFontHeightInPoints((short)12);
            // 字体样式3 -- 普通字体
            Font font3 = book.createFont();
            font3.setBold(false);    // 不加粗
            font3.setFontHeightInPoints((short)12);


            // 单元格样式1 -- 第一行大标题居中、无网格线、加粗字体
            CellStyle cellStyle1 = book.createCellStyle();  // 标题样式
            cellStyle1.setAlignment(HorizontalAlignment.CENTER);        // 水平居中
            cellStyle1.setVerticalAlignment(VerticalAlignment.CENTER);  // 垂直居中
            cellStyle1.setFont(font1);
            // cellStyle1.setFillForegroundColor(IndexedColors.GOLD.getIndex());
            // cellStyle1.setFillPattern(CellStyle.SOLID_FOREGROUND);

            // 单元格样式2 -- 第二行时间段水平居左、无网格线、普通字体
            CellStyle cellStyle2 = book.createCellStyle();
            cellStyle2.setAlignment(HorizontalAlignment.LEFT);          // 水平居左
            cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);  // 垂直居中
            cellStyle2.setFont(font3);

            // 单元格样式3 -- 有网格线、居中、普通字体加粗
            CellStyle cellStyle3 = book.createCellStyle();
            cellStyle3.setBorderLeft(BorderStyle.THIN);
            cellStyle3.setBorderRight(BorderStyle.THIN);
            cellStyle3.setBorderTop(BorderStyle.THIN);
            cellStyle3.setBorderBottom(BorderStyle.THIN);
            cellStyle3.setAlignment(HorizontalAlignment.CENTER);        // 水平居中
            cellStyle3.setVerticalAlignment(VerticalAlignment.CENTER);  // 垂直居中
            cellStyle3.setFont(font2);

            // 单元格样式4 -- 有网格线、居中、普通字体不加粗
            CellStyle cellStyle4 = book.createCellStyle();
            cellStyle4.setBorderLeft(BorderStyle.THIN);
            cellStyle4.setBorderRight(BorderStyle.THIN);
            cellStyle4.setBorderTop(BorderStyle.THIN);
            cellStyle4.setBorderBottom(BorderStyle.THIN);
            cellStyle4.setAlignment(HorizontalAlignment.CENTER);        // 水平居中
            cellStyle4.setVerticalAlignment(VerticalAlignment.CENTER);  // 垂直居中
            cellStyle4.setFont(font3);


            // 创建第一个sheet页、设置列宽
            Sheet sheet1= book.createSheet("sheet1");
            sheet1.setColumnWidth(0,8000);  // 下辖
            sheet1.setColumnWidth(1,6000);  // 机构数量
            sheet1.setColumnWidth(2,6000);  // 暂存点数量
            sheet1.setColumnWidth(3,7000);  // 医疗废物产生量（kg）
            sheet1.setColumnWidth(4,7000);  // 医疗废物转运量（kg）
            sheet1.setColumnWidth(5,7000);  // 暂存量（kg）

            // 第一行（标题：石家庄各区县医废产量、转运量、暂存量统计）
            Row row1 = sheet1.createRow(0);
            row1.setHeight((short) 750);     // 行高
            Cell row1cell0 = row1.createCell(0);
            row1cell0.setCellValue("石家庄各区县医废产量、转运量、暂存量统计");
            row1cell0.setCellStyle(cellStyle1);

            // 第二行（ 2020年8月5日 至 2020年x月x日 xx:xx:xx ）
            Row row2 = sheet1.createRow(1);
            row2.setHeight((short) 500);     // 行高
            Cell row2cell0 = row2.createCell(0);
            row2cell0.setCellValue( excelStartTime +" 至 " + excelEndTime );
            row2cell0.setCellStyle(cellStyle2);

            // 第三行 （ 下辖 s1、机构数量 i2、暂存点数量 i1、医疗废物产生量（kg）d1、医疗废物转运量（kg）d2、暂存量（kg）d3 ）
            Row row3 = sheet1.createRow(2);
            row3.setHeight((short) 500);     // 行高
            Cell row3cell0 = row3.createCell(0);
            row3cell0.setCellValue( "下辖" );
            row3cell0.setCellStyle(cellStyle3);
            Cell row3cell1 = row3.createCell(1);
            row3cell1.setCellValue( "机构数量" );
            row3cell1.setCellStyle(cellStyle3);
            Cell row3cell2 = row3.createCell(2);
            row3cell2.setCellValue( "暂存点数量" );
            row3cell2.setCellStyle(cellStyle3);
            Cell row3cell3 = row3.createCell(3);
            row3cell3.setCellValue( "医疗废物产生量（kg）" );
            row3cell3.setCellStyle(cellStyle3);
            Cell row3cell4 = row3.createCell(4);
            row3cell4.setCellValue( "医疗废物转运量（kg）" );
            row3cell4.setCellStyle(cellStyle3);
            Cell row3cell5 = row3.createCell(5);
            row3cell5.setCellValue( "暂存量（kg）" );
            row3cell5.setCellStyle(cellStyle3);

            // 生成第四行及之后
            for (int i = 3; i < (staticlist.size() + 3) ; i++) {
                Row sh1rowi = sheet1.createRow(i);
                sh1rowi.setHeight((short) 500);     // 行高
                Cell rowicell0 = sh1rowi.createCell(0);
                rowicell0.setCellValue( staticlist.get(i - 3).getS1() );
                rowicell0.setCellStyle(cellStyle4);
                Cell rowicell1 = sh1rowi.createCell(1);
                rowicell1.setCellValue( String.valueOf(staticlist.get(i - 3).getI2()) );
                rowicell1.setCellStyle(cellStyle4);
                Cell rowicell2 = sh1rowi.createCell(2);
                rowicell2.setCellValue( String.valueOf(staticlist.get(i - 3).getI1()) );
                rowicell2.setCellStyle(cellStyle4);
                Cell rowicell3 = sh1rowi.createCell(3);
                rowicell3.setCellValue( staticlist.get(i - 3).getD1() );
                rowicell3.setCellStyle(cellStyle4);
                Cell rowicell4 = sh1rowi.createCell(4);
                rowicell4.setCellValue( staticlist.get(i - 3).getD2() );
                rowicell4.setCellStyle(cellStyle4);
                Cell rowicell5 = sh1rowi.createCell(5);
                rowicell5.setCellValue( staticlist.get(i - 3).getD3() );
                rowicell5.setCellStyle(cellStyle4);
            }

            // 生成合计行
            Row rowLast = sheet1.createRow(3 + staticlist.size() );
            rowLast.setHeight((short) 500);     // 行高
            Cell rowLcell0 = rowLast.createCell(0);
            rowLcell0.setCellValue( "合计" );
            rowLcell0.setCellStyle(cellStyle3);
            // 计算合计
            int hosNum = 0,stagingNum = 0;
            double d1 = 0.0,d2 = 0.0,d3 = 0.0;
            for (int i = 0; i < staticlist.size(); i++) {
                hosNum += Integer.parseInt(staticlist.get(i).getI2() + "");
                stagingNum += Integer.parseInt(staticlist.get(i).getI1() + "");
                d1 += Double.parseDouble(staticlist.get(i).getD1() + "");
                d2 += Double.parseDouble(staticlist.get(i).getD2() + "");
                d3 += Double.parseDouble(staticlist.get(i).getD3() + "");
            }
            Cell rowLcell1 = rowLast.createCell(1);
            rowLcell1.setCellValue( hosNum + "" );
            rowLcell1.setCellStyle(cellStyle3);
            Cell rowLcell2 = rowLast.createCell(2);
            rowLcell2.setCellValue( stagingNum + "" );
            rowLcell2.setCellStyle(cellStyle3);
            Cell rowLcell3 = rowLast.createCell(3);
            rowLcell3.setCellValue( new BigDecimal(d1).setScale(2,BigDecimal.ROUND_HALF_UP).doubleValue() +"" );
            rowLcell3.setCellStyle(cellStyle3);
            Cell rowLcell4 = rowLast.createCell(4);
            rowLcell4.setCellValue( new BigDecimal(d2).setScale(2,BigDecimal.ROUND_HALF_UP).doubleValue() +"" );
            rowLcell4.setCellStyle(cellStyle3);
            Cell rowLcell5 = rowLast.createCell(5);
            rowLcell5.setCellValue( new BigDecimal(d3).setScale(2,BigDecimal.ROUND_HALF_UP).doubleValue() +"" );
            rowLcell5.setCellStyle(cellStyle3);


            // 合并单元格，参数1：起始行   参数2：终止行   参数3：起始列   参数4：终止列
            CellRangeAddress region1 = new CellRangeAddress(0, 0, (short) 0, (short) 5);
            CellRangeAddress region2 = new CellRangeAddress(1, 1, (short) 0, (short) 5);
            sheet1.addMergedRegion(region1);
            sheet1.addMergedRegion(region2);

            return book;

        } catch (Exception e) {
            System.out.println("-----导出excel异常了！-------");
            e.printStackTrace();

            return null;
        }


    }


    /**
     *  生成excel -- 医废种类分析  27行  9列
     *  37551a22-2322-4faa-aa2f-f6f0b22c13cf
     */
    private SXSSFWorkbook createCityYFTypeStaticExcel(List<Report> staticlist, String excelStartTime, String excelEndTime){
        SXSSFWorkbook book = null;
        try {
            // 输出流
            // os = new FileOutputStream(excelPath);
            // 创建工作区(2007)       创建工作区(97-2003)    HSSFWorkbook
            book = new SXSSFWorkbook(800);

            // 字体样式1 -- 加粗标题字体
            Font font1 = book.createFont();
            font1.setBold(true);    // 加粗
            font1.setFontHeightInPoints((short)13);
            // 字体样式2 -- 普通加粗字体
            Font font2 = book.createFont();
            font2.setBold(true);    // 加粗
            font2.setFontHeightInPoints((short)12);
            // 字体样式3 -- 普通字体
            Font font3 = book.createFont();
            font3.setBold(false);    // 不加粗
            font3.setFontHeightInPoints((short)12);


            // 单元格样式1 -- 第一行大标题居中、无网格线、加粗字体
            CellStyle cellStyle1 = book.createCellStyle();  // 标题样式
            cellStyle1.setAlignment(HorizontalAlignment.CENTER);        // 水平居中
            cellStyle1.setVerticalAlignment(VerticalAlignment.CENTER);  // 垂直居中
            cellStyle1.setFont(font1);

            // 单元格样式2 -- 第二行时间段水平居左、无网格线、普通字体
            CellStyle cellStyle2 = book.createCellStyle();
            cellStyle2.setAlignment(HorizontalAlignment.LEFT);          // 水平居左
            cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);  // 垂直居中
            cellStyle2.setFont(font3);

            // 单元格样式3 -- 有网格线、居中、普通字体加粗
            CellStyle cellStyle3 = book.createCellStyle();
            cellStyle3.setBorderLeft(BorderStyle.THIN);
            cellStyle3.setBorderRight(BorderStyle.THIN);
            cellStyle3.setBorderTop(BorderStyle.THIN);
            cellStyle3.setBorderBottom(BorderStyle.THIN);
            cellStyle3.setAlignment(HorizontalAlignment.CENTER);        // 水平居中
            cellStyle3.setVerticalAlignment(VerticalAlignment.CENTER);  // 垂直居中
            cellStyle3.setFont(font2);

            // 单元格样式4 -- 有网格线、居中、普通字体不加粗
            CellStyle cellStyle4 = book.createCellStyle();
            cellStyle4.setBorderLeft(BorderStyle.THIN);
            cellStyle4.setBorderRight(BorderStyle.THIN);
            cellStyle4.setBorderTop(BorderStyle.THIN);
            cellStyle4.setBorderBottom(BorderStyle.THIN);
            cellStyle4.setAlignment(HorizontalAlignment.CENTER);        // 水平居中
            cellStyle4.setVerticalAlignment(VerticalAlignment.CENTER);  // 垂直居中
            cellStyle4.setFont(font3);


            // 创建第一个sheet页、设置列宽
            Sheet sheet1= book.createSheet("sheet1");
            sheet1.setColumnWidth(0,8000);  // 下辖
            sheet1.setColumnWidth(1,6000);  // 机构数量
            sheet1.setColumnWidth(2,6000);  // 暂存点数量
            sheet1.setColumnWidth(3,5000);  // 病理性（kg）
            sheet1.setColumnWidth(4,5000);  // 损伤性（kg）
            sheet1.setColumnWidth(5,5000);  // 感染性（kg）
            sheet1.setColumnWidth(6,5000);  // 药物性（kg）
            sheet1.setColumnWidth(7,5000);  // 化学性（kg）
            sheet1.setColumnWidth(8,5000);  // 和计

            // 第一行（标题：石家庄各区县医废产量、转运量、暂存量统计）
            Row row1 = sheet1.createRow(0);
            row1.setHeight((short) 750);     // 行高
            Cell row1cell0 = row1.createCell(0);
            row1cell0.setCellValue("石家庄各区县各医废类型统计");
            row1cell0.setCellStyle(cellStyle1);

            // 第二行（ 2020年8月5日 至 2020年x月x日 xx:xx:xx ）
            Row row2 = sheet1.createRow(1);
            row2.setHeight((short) 500);     // 行高
            Cell row2cell0 = row2.createCell(0);
            row2cell0.setCellValue( excelStartTime +" 至 " + excelEndTime );
            row2cell0.setCellStyle(cellStyle2);

            // 第三行 （ 下辖 s1、机构数量 i2、暂存点数量 i1、医疗废物产生量（kg）d1、医疗废物转运量（kg）d2、暂存量（kg）d3 ）
            Row row3 = sheet1.createRow(2);
            row3.setHeight((short) 500);     // 行高
            Cell row3cell0 = row3.createCell(0);
            row3cell0.setCellValue( "下辖" );
            row3cell0.setCellStyle(cellStyle3);
            Cell row3cell1 = row3.createCell(1);
            row3cell1.setCellValue( "机构数量" );
            row3cell1.setCellStyle(cellStyle3);
            Cell row3cell2 = row3.createCell(2);
            row3cell2.setCellValue( "暂存点数量" );
            row3cell2.setCellStyle(cellStyle3);
            Cell row3cell3 = row3.createCell(3);
            row3cell3.setCellValue( "病理性（kg）" );
            row3cell3.setCellStyle(cellStyle3);
            Cell row3cell4 = row3.createCell(4);
            row3cell4.setCellValue( "损伤性（kg）" );
            row3cell4.setCellStyle(cellStyle3);
            Cell row3cell5 = row3.createCell(5);
            row3cell5.setCellValue( "感染性（kg）" );
            row3cell5.setCellStyle(cellStyle3);
            Cell row3cell6 = row3.createCell(6);
            row3cell6.setCellValue( "感染性（kg）" );
            row3cell6.setCellStyle(cellStyle3);
            Cell row3cell7 = row3.createCell(7);
            row3cell7.setCellValue( "化学性（kg）" );
            row3cell7.setCellStyle(cellStyle3);
            Cell row3cell8 = row3.createCell(8);
            row3cell8.setCellValue( "合计" );
            row3cell8.setCellStyle(cellStyle3);

            // 生成第四行及之后
            for (int i = 3; i < (staticlist.size() + 3) ; i++) {
                Row sh1rowi = sheet1.createRow(i);
                sh1rowi.setHeight((short) 500);     // 行高
                Cell rowicell0 = sh1rowi.createCell(0);
                rowicell0.setCellValue( staticlist.get(i - 3).getS1() );
                rowicell0.setCellStyle(cellStyle4);
                Cell rowicell1 = sh1rowi.createCell(1);
                rowicell1.setCellValue( String.valueOf(staticlist.get(i - 3).getI2()) );
                rowicell1.setCellStyle(cellStyle4);
                Cell rowicell2 = sh1rowi.createCell(2);
                rowicell2.setCellValue( String.valueOf(staticlist.get(i - 3).getI1()) );
                rowicell2.setCellStyle(cellStyle4);
                Cell rowicell3 = sh1rowi.createCell(3);
                rowicell3.setCellValue( staticlist.get(i - 3).getD1() );
                rowicell3.setCellStyle(cellStyle4);
                Cell rowicell4 = sh1rowi.createCell(4);
                rowicell4.setCellValue( staticlist.get(i - 3).getD2() );
                rowicell4.setCellStyle(cellStyle4);
                Cell rowicell5 = sh1rowi.createCell(5);
                rowicell5.setCellValue( staticlist.get(i - 3).getD3() );
                rowicell5.setCellStyle(cellStyle4);
                Cell rowicell6 = sh1rowi.createCell(6);
                rowicell6.setCellValue( staticlist.get(i - 3).getD4() );
                rowicell6.setCellStyle(cellStyle4);
                Cell rowicell7 = sh1rowi.createCell(7);
                rowicell7.setCellValue( staticlist.get(i - 3).getD5() );
                rowicell7.setCellStyle(cellStyle4);
                Cell rowicell8 = sh1rowi.createCell(8);
                rowicell8.setCellValue( staticlist.get(i - 3).getD7() );
                rowicell8.setCellStyle(cellStyle4);
            }

            // 生成合计行
            Row rowLast = sheet1.createRow(3 + staticlist.size() );
            rowLast.setHeight((short) 500);     // 行高
            Cell rowLcell0 = rowLast.createCell(0);
            rowLcell0.setCellValue( "合计" );
            rowLcell0.setCellStyle(cellStyle3);
            // 计算合计
            int hosNum = 0, stagingNum = 0;
            double d1 = 0.0, d2 = 0.0, d3 = 0.0, d4 = 0.0, d5 = 0.0, d7 = 0.0;
            for (int i = 0; i < staticlist.size(); i++) {
                hosNum += Integer.parseInt(staticlist.get(i).getI2() + "");
                stagingNum += Integer.parseInt(staticlist.get(i).getI1() + "");
                d1 += Double.parseDouble(staticlist.get(i).getD1() + "");
                d2 += Double.parseDouble(staticlist.get(i).getD2() + "");
                d3 += Double.parseDouble(staticlist.get(i).getD3() + "");
                d4 += Double.parseDouble(staticlist.get(i).getD4() + "");
                d5 += Double.parseDouble(staticlist.get(i).getD5() + "");
                d7 += Double.parseDouble(staticlist.get(i).getD7() + "");
            }
            Cell rowLcell1 = rowLast.createCell(1);
            rowLcell1.setCellValue( hosNum + "" );
            rowLcell1.setCellStyle(cellStyle3);
            Cell rowLcell2 = rowLast.createCell(2);
            rowLcell2.setCellValue( stagingNum + "" );
            rowLcell2.setCellStyle(cellStyle3);
            Cell rowLcell3 = rowLast.createCell(3);
            rowLcell3.setCellValue( new BigDecimal(d1).setScale(2,BigDecimal.ROUND_HALF_UP).doubleValue() +"" );
            rowLcell3.setCellStyle(cellStyle3);
            Cell rowLcell4 = rowLast.createCell(4);
            rowLcell4.setCellValue( new BigDecimal(d2).setScale(2,BigDecimal.ROUND_HALF_UP).doubleValue() +"" );
            rowLcell4.setCellStyle(cellStyle3);
            Cell rowLcell5 = rowLast.createCell(5);
            rowLcell5.setCellValue( new BigDecimal(d3).setScale(2,BigDecimal.ROUND_HALF_UP).doubleValue() +"" );
            rowLcell5.setCellStyle(cellStyle3);

            Cell rowLcell6 = rowLast.createCell(6);
            rowLcell6.setCellValue( new BigDecimal(d4).setScale(2,BigDecimal.ROUND_HALF_UP).doubleValue() +"" );
            rowLcell6.setCellStyle(cellStyle3);
            Cell rowLcell7 = rowLast.createCell(7);
            rowLcell7.setCellValue( new BigDecimal(d5).setScale(2,BigDecimal.ROUND_HALF_UP).doubleValue() +"" );
            rowLcell7.setCellStyle(cellStyle3);
            Cell rowLcell8 = rowLast.createCell(8);
            rowLcell8.setCellValue( new BigDecimal(d7).setScale(2,BigDecimal.ROUND_HALF_UP).doubleValue() +"" );
            rowLcell8.setCellStyle(cellStyle3);


            // 合并单元格，参数1：起始行   参数2：终止行   参数3：起始列   参数4：终止列
            CellRangeAddress region1 = new CellRangeAddress(0, 0, (short) 0, (short) 8);
            CellRangeAddress region2 = new CellRangeAddress(1, 1, (short) 0, (short) 8);
            sheet1.addMergedRegion(region1);
            sheet1.addMergedRegion(region2);

            return book;

        } catch (Exception e) {
            System.out.println("-----导出excel2异常了！-------");
            e.printStackTrace();

            return null;
        }

    }




    // 定时查询某天的医废统计
    //a1ea8b42-6d0b-411d-a906-1e21d6e25382
    @RequestMapping("/getDistrictYFStatistical")
    @ResponseBody
    public ResponseData getDistrictYFStatistical(Long districtId,String date) {
        // 地区id
        if( districtId == null ){
            districtId = 1301L;
        }
        // 日期  yyyy-MM-dd
        if( StringUtils.isEmpty(date) ){
            date = sdfday.format( new Date(new Date().getTime() - 86400000) ) ;
        }else{
            try{
                sdfday.parse(date);
            }catch (Exception e){
                date = sdfday.format( new Date(new Date().getTime() - 86400000) ) ;
            }
        }

        yfReportsService = (IReportservice) ApplicationContextUtil.getBean("yfReportsService");

        List<YfDistrictStatistical> list = yfReportsService.getDistrictYFStatistical(districtId, date);

        yfDistrictStatisticalService = (IYfDistrictStatisticalService) ApplicationContextUtil.
                getBean("yfDistrictStatisticalService");

        if( list != null && !list.isEmpty() ){
            for (int i = 0; i < list.size(); i++) {
                list.get(i).setCreateBy("admin");
            }
            // 先根据地区及日期逻辑删除已经存在的统计
            yfDistrictStatisticalService.deleteYfDistrictStatisticalByDate(districtId, date);

            // 批量添加地区统计
            yfDistrictStatisticalService.insertYfDistrictStatisticalList(list);
        }


        return ResponseData.successY("成功");
    }



    // 同步系统对接的医院的科室
    @RequestMapping("/synchronizeHodpitalKS")
    @ResponseBody
    public ResponseData synchronizeHodpitalKS(String date){
        // 日期yyyy-MM-dd取大于等于某天    1、凌晨以后执行，不指定日期则取近两三天
        if( StringUtils.isEmpty(date) ){
            date = sdfday.format( new Date(new Date().getTime() - 172800000) ) ;    // 两天
        }else{
            try{
                sdfday.parse(date);
            }catch (Exception e){
                date = sdfday.format( new Date(new Date().getTime() - 172800000) ) ;
            }
        }

        System.out.println("   查询日期： " + date  );

        yfDistrictStatisticalService = (IYfDistrictStatisticalService) ApplicationContextUtil.
                getBean("yfDistrictStatisticalService");

        // 先查询需要同步科室的医疗机构  parentId 医院id    parentName 医院名称    deptId 暂存点id
        List<SysDept> deptlist = yfDistrictStatisticalService.getNeedSyncKSDeDept();
        if( deptlist == null || deptlist.isEmpty() ){
            return ResponseData.successY("机构表未查到需要同步科室的医院");
        }

        // 根据暂存点id去医废表里查询日期段内的科室（因医废表没有医院id查询出的list没有医院id）
        Long [] stagingIds = deptlist.stream().map(SysDept::getDeptId).collect(Collectors.toList()).toArray(new Long[deptlist.size()]);
//        for (int i = 0; i < stagingIds.length; i++) {
//            System.out.println("   ----- " + stagingIds[i] );
//        }
        List<YfDept> yfkslist = yfDistrictStatisticalService.getKSListFromYFByStagingIds(stagingIds, date);
        if( yfkslist == null || yfkslist.isEmpty() ){
            return ResponseData.successY("医废表未查询到科室需要同步");
        }

        // 根据deptlist里的医院id去科室表查询这些医院科室
        List<Long> hospitalIdslist = new ArrayList<>();
        for (SysDept mDeptlist : deptlist) {
            if (!hospitalIdslist.contains(mDeptlist.getParentId())) {
                hospitalIdslist.add(mDeptlist.getParentId());
            }
        }
        Long [] hospitalIds = hospitalIdslist.toArray(new Long[hospitalIdslist.size()]);
        List<YfDept> hoskslist = yfDistrictStatisticalService.getKSListFromKSTableByHospitalIds(hospitalIds);

        // 找出yfkslist(医废表中的科室)里有，hoskslist（医院科室表中的科室）里没有的科室添加到 needSyncKSList
        List<YfDept> needSyncKSList = new ArrayList<>();
        if ( hoskslist == null || hoskslist.isEmpty() ){    // 根据医院id未查询到科室直接把医废表科室全部添加到科室表
            needSyncKSList.addAll(yfkslist);
        }else{
            for (YfDept mYfkslist : yfkslist) {         // 医废表查询到的科室
                int num = 0;
                for (YfDept mHoskslist : hoskslist) {   // 科室表查询到的科室
                    if (mYfkslist.getDeptId().longValue() == mHoskslist.getDeptId().longValue()) {
                        num ++;
                        break;
                    }
                }
                if( num == 0 ){
                    needSyncKSList.add(mYfkslist);
                }
            }
        }

        // 把这些科室添加到科室表：科室需要的字段：科室id、医院id、科室类型10、标准科室、科室名称、负责人（选）、
        // 分组id及名称（固定）、楼号（固定1）、status、del_flag(固定)、创建时间取系统时间
        if( needSyncKSList.isEmpty() ){
            return ResponseData.successY("没有医院科室需要同步");
        }
        // 逐条添加科室
        for (YfDept mNeedSyncKSBean : needSyncKSList) {
            try {
                // 医废表查询出的科室只有暂存点id无医院id，先去查询医院id,从 deptlist 中匹配，parentId 医院id    parentName 医院名称    deptId 暂存点id
                for (SysDept mDeptBean : deptlist) {
                    if (mNeedSyncKSBean.getStagingId().longValue() == mDeptBean.getDeptId().longValue()) {
                        mNeedSyncKSBean.setHospitalId( mDeptBean.getParentId() );
                        mNeedSyncKSBean.setGroupName( mDeptBean.getParentName() );
                    }
                }
                if( mNeedSyncKSBean.getHospitalId() != null ){
                    // 把从医废表抓取的科室添加到科室表
                    System.out.println(" --- 添加科室  科室id： " + mNeedSyncKSBean.getDeptId() + "  名称： " + mNeedSyncKSBean.getDeptName()
                            +  "  标准科室： " + mNeedSyncKSBean.getStandardDeptCode() + "  医院id：" + mNeedSyncKSBean.getHospitalId()  );
                    if( StringUtils.isEmpty(mNeedSyncKSBean.getType()) ){
                        mNeedSyncKSBean.setType("10");
                    }
                    yfDistrictStatisticalService.insertKStoKSTable(mNeedSyncKSBean);
                }
            } catch (DuplicateKeyException e) {   // 主键约束异常
                System.out.println(" --- 添加科室时：科室id： " + mNeedSyncKSBean.getDeptId() + " -- " + mNeedSyncKSBean.getDeptName() + " 主键冲突 ！");
            }
        }


        return ResponseData.successY("成功");
    }


}
